﻿Imports System.Data.OleDb
Public Class TaiKhoanNganHangDAO
    Shared conn_str As String = My.Settings.ConnectionString
    Shared Function GetAll() As DataTable
        Dim dt As New DataTable
        Dim query As String = "select * from TaiKhoanNganHang join nganhang on nganhang.manganhang = taikhoannganhang.manganhang"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function GetAll(ByVal MaUser As Integer) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select * from TaiKhoanNganHang join nganhang on nganhang.manganhang = taikhoannganhang.manganhang where MaUser = ?"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.AddWithValue("@MaUser", MaUser)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function IsExists(ByVal MaUser As Integer, ByVal MaNganHang As Integer) As Integer
        Dim count As Integer
        Dim query As String = "select count(*) from TaiKhoanNganHang where MaUser = ? AND MaNganHang = ?"
        Try
            Dim conn As New OleDbConnection(conn_str)
            Dim cmd As New OleDbCommand(query, conn)
            cmd.Parameters.AddWithValue("@MaUser", MaUser)
            cmd.Parameters.AddWithValue("@MaNganHang", MaNganHang)
            conn.Open()
            Integer.TryParse(cmd.ExecuteScalar(), count)
            conn.Close()
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return count
    End Function
    Shared Sub UpdateSoTien(ByVal MaNganHang As Integer, ByVal SoTien As Double, Optional Thu As Boolean = True)
        Dim query As String
        If Thu Then
            query = "update TaiKhoanNganHang set SoTien = SoTien + ? where MaNganHang = ?"
        Else
            query = "update TaiKhoanNganHang set SoTien = SoTien - ? where MaNganHang = ?"
        End If
        Try
            Dim conn As New OleDbConnection(conn_str)
            conn.Open()
            Dim cmd As New OleDbCommand(query, conn)
            cmd.Parameters.AddWithValue("@MaNganHang", MaNganHang)
            cmd.Parameters.AddWithValue("@SoTien", SoTien)
            cmd.ExecuteNonQuery()
            conn.Close()
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
    End Sub
    Shared Function TongTien(ByVal MaUser As Integer) As Double
        Dim tb As Double
        Dim query As String = "select sum(sotien) from TaiKhoanNganHang " & _
        "where MaUser = ?"
        Try
            Using conn As New OleDbConnection(conn_str)
                Dim cmd As New OleDbCommand(query, conn)
                cmd.Parameters.AddWithValue("@MaUser", MaUser)
                conn.Open()
                Double.TryParse(cmd.ExecuteScalar().ToString(), tb)
                conn.Close()
            End Using
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return tb
    End Function
    Shared Sub CongTien(ByVal MaUser As Integer, ByVal MaNganHang As Integer, ByVal SoTien As Double)
        Dim query As String = "update TaiKhoanNganHang set sotien = sotien + ? where MaUser = ? and MaNganHang = ?"
        Try
            Using conn As New OleDbConnection(conn_str)
                Dim cmd As New OleDbCommand(query, conn)
                cmd.Parameters.AddWithValue("@SoTien", SoTien)
                cmd.Parameters.AddWithValue("@MaUser", MaUser)
                cmd.Parameters.AddWithValue("@MaNganHang", MaNganHang)
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()
            End Using
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
    End Sub
    Shared Sub TruTien(ByVal MaUser As Integer, ByVal MaNganHang As Integer, ByVal SoTien As Double)
        Dim query As String = "update TaiKhoanNganHang set sotien = sotien - ? where MaUser = ? and MaNganHang = ?"
        Try
            Using conn As New OleDbConnection(conn_str)
                Dim cmd As New OleDbCommand(query, conn)
                cmd.Parameters.AddWithValue("@SoTien", SoTien)
                cmd.Parameters.AddWithValue("@MaUser", MaUser)
                cmd.Parameters.AddWithValue("@MaNganHang", MaNganHang)
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()
            End Using
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
    End Sub
End Class
